-- 테이블 생성 부분입니다. by Harry

DROP TABLE time_line cascade constraints;
--사랑의 TIME LINE
CREATE TABLE time_line (
	time_line_seq	NUMBER			NOT NULL,	--	Time Line 별 seq
	time_line_name	VARCHAR2(40)	NOT	NULL,	--	Time Line 이름
	from_nick 		VARCHAR2(20)	NOT NULL,	--	보낸사람 닉네임
	to_nick 		VARCHAR2(20)	NOT NULL,	--	받는사람 닉네임
	from_id 		VARCHAR2(20)	NOT NULL,	--	보낸사람 아이디
	to_id 			VARCHAR2(20)	NOT NULL,	--	받는사람 아이디
	send_time		VARCHAR2(20)	NOT NULL,	--	보낸 시간
	is_read			NUMBER			default 0	NOT NULL,	--	해당 메세지 읽음 여부(보류)
	message			VARCHAR2(1000),				--	보낸 메세지
	picture_path	VARCHAR2(100),				--	보낸 사진
	
	CONSTRAINT time_line_pk	PRIMARY	KEY(time_line_name, time_line_seq),
	CONSTRAINT time_from_id_fk 	FOREIGN KEY(from_id) 	REFERENCES member(id) 		ON  DELETE  CASCADE,
	CONSTRAINT time_to_id_fk 	FOREIGN KEY(to_id) 		REFERENCES member(id) 		ON  DELETE  CASCADE		
);

drop SEQUENCE chatting_seq;
--Chatting Sequence
CREATE SEQUENCE chatting_seq	--	CHATTING table에 사용될 seq
nocycle
nocache;

DROP TABLE chatting cascade constraints;
--실시간 주고받을수 있게 할 table
CREATE TABLE chatting (
	chatting_seq	NUMBER			NOT NULL,	--	각 row구분용 seq
	time_line_name	VARCHAR2(40)	NOT	NULL,	--	Time Line 이름 (보낸이가 어떤 타임라인에 썼는가)
	from_nick 		VARCHAR2(20)	NOT NULL,	--	보낸사람 닉네임
	to_nick 		VARCHAR2(20)	NOT NULL,	--	받는사람 닉넥임
	from_id 		VARCHAR2(20)	NOT NULL,	--	보낸사람 아이디
	to_id 			VARCHAR2(20)	NOT NULL,	--	받는사람 아이디
	send_time		VARCHAR2(20)	NOT NULL,	--	보낸 시간
	message			VARCHAR2(1000),				--	보낸 메세지
	picture_path	VARCHAR2(100),				--	보낸 사진
	
	CONSTRAINT chatt_pk 	PRIMARY KEY(chatting_seq),
	CONSTRAINT from_id_fk 	FOREIGN KEY(from_id) 	REFERENCES member(id) 		ON  DELETE  CASCADE,
	CONSTRAINT to_id_fk 	FOREIGN KEY(to_id) 		REFERENCES member(id) 		ON  DELETE  CASCADE 
);

-- 고객센터
DROP TABLE cscenter cascade constraints;
CREATE TABLE cscenter (
	cscenter_no   NUMBER       NOT NULL, -- 글번호
	id         VARCHAR2(20)  NOT NULL, -- 아이디
	nickname   VARCHAR2(36)  NOT NULL, -- 닉네임
	email		VARCHAR2(50)	NOT NULL, --이메일
	divide     	INTEGER       NOT NULL, -- 구분 - 5:notice/6:faq/7:qna
	divide_no 	NUMBER       DEFAULT 0 NOT NULL, -- 구분별 글번호
	title      	VARCHAR2(255)  NOT NULL, -- 제목
	content    	CLOB NOT NULL, -- 내용
	result     	VARCHAR2(4)	NULL,     -- 처리결과 (NULL)/DO/DONE
	view_cnt    NUMBER       NOT NULL, -- 조회수
	cre_date   	VARCHAR2(14)	 DEFAULT to_char(sysdate, 'yyyymmddhh24miss') NOT NULL, -- 작성일
	del_date   	VARCHAR2(14)     NULL      -- 삭제일
	, constraint cscenter_no_pk primary key(cscenter_no)
	, constraint cscenter_member_id_fk foreign key(id) references member(id) on delete cascade
);
drop sequence cscenter_no_seq;
create sequence cscenter_no_seq
nocycle
nocache;
select * from cscenter;

drop sequence cscenter_divide_no5_seq;
create sequence cscenter_divide_no5_seq
nocycle
nocache;

drop sequence cscenter_divide_no6_seq;
create sequence cscenter_divide_no6_seq
nocycle
nocache;

drop sequence cscenter_divide_no7_seq;
create sequence cscenter_divide_no7_seq
nocycle
nocache;

-- 고객센터 답글
CREATE TABLE "reply" (
	reply_no    NUMBER       NOT NULL, -- 댓글 번호
	cscenter_no NUMBER        NOT NULL, -- 게시판 번호
	id          VARCHAR2(20)  NOT NULL, -- 아이디
	nickname    VARCHAR2(36)  NOT NULL, -- 닉네임
	relevel     NUMBER        NOT NULL, -- 답변 레벨
	content     VARCHAR2(255) NOT NULL, -- 내용
	cre_date    VARCHAR2(14)	DEFAULT to_char(sysdate, 'yyyymmddhh24miss') NOT NULL -- 작성일
	, constraint reply_no_pk primary key(reply_no)
	, constraint reply_cscenter_no_fk foreign key(cscenter_no) references cscenter(cscenter_no) on delete cascade
	, constraint reply_member_id_fk foreign key(id) references member(id) on delete cascade
);
drop sequence reply_no_seq;
create sequence reply_no_seq
nocycle
nocache;

-- 신고하기
DROP TABLE declaration;
CREATE TABLE declaration (
	declaration_no   NUMBER       NOT NULL, -- 글번호
	id           VARCHAR2(20)  NOT NULL, -- 아이디
	nickname     VARCHAR2(36)  NOT NULL, -- 닉네임
	content      VARCHAR2(1000) NOT NULL, -- 사유
	assaulter_id VARCHAR2(20)   NOT NULL, -- 가해자
	assaulter_nickname VARCHAR2(30)   NOT NULL, -- 가해자
	result       VARCHAR2(1)    NULL,     -- 처리결과 (NULL):미처리 / 0:신고안함 / 1:신고함
	cre_date     VARCHAR2(14) DEFAULT to_char(sysdate, 'yyyymmddhh24miss') NOT NULL, -- 작성일
	del_date     VARCHAR2(14)     NULL      -- 삭제일
	, constraint declaration_no_pk primary key(declaration_no)
	, constraint declaration_member_id_fk foreign key(id) references member(id) on delete cascade
);
select * from declaration;
drop sequence declaration_no_seq;
create sequence declaration_no_seq
nocycle
nocache;

--친구 리스트
CREATE TABLE  "FRIEND_LIST" 
   (	"NO" NUMBER(3,0) NOT NULL ENABLE, 
	"MY_ID" VARCHAR2(50) NOT NULL ENABLE, 
	"FRIEND_ID" VARCHAR2(50) NOT NULL ENABLE, 
	 CONSTRAINT "FRIEND_LIST_PK" PRIMARY KEY ("NO") ENABLE
   );
   CREATE OR REPLACE TRIGGER  "BI_FRIEND_LIST" 
  before insert on "FRIEND_LIST"               
  for each row  
begin   
  if :NEW."NO" is null then 
    select "FRIEND_LIST_SEQ".nextval into :NEW."NO" from dual; 
  end if; 
end; 

CREATE SEQUENCE   "FRIEND_LIST_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999 INCREMENT BY 1 START WITH 37 NOCACHE  NOORDER  NOCYCLE ;

--설문 응답 테이블
CREATE TABLE  "ANSWER" 
   (	"ID" VARCHAR2(20) NOT NULL ENABLE, 
	"NICKNAME" VARCHAR2(36) NOT NULL ENABLE, 
	"SURVEY_NO" NUMBER(10,0) NOT NULL ENABLE, 
	"SURVEY_ANSWER" NUMBER(10,0) NOT NULL ENABLE, 
	 CONSTRAINT "ANSWER_PK" PRIMARY KEY ("ID", "SURVEY_NO") ENABLE
   ) ;
   
   --쪽지 테이블
 CREATE TABLE  "MESSAGE" 
   (	"NUM" NUMBER(10,0) NOT NULL ENABLE, 
	"FROM_ID" VARCHAR2(50) NOT NULL ENABLE, 
	"TO_ID" VARCHAR2(50) NOT NULL ENABLE, 
	"CONTENT" VARCHAR2(3000) NOT NULL ENABLE, 
	"DAYS" VARCHAR2(50) NOT NULL ENABLE, 
	"TITLE" VARCHAR2(1000) NOT NULL ENABLE, 
	"FLAG" NUMBER(2,0), 
	 CONSTRAINT "MESSAGE_PK" PRIMARY KEY ("NUM") ENABLE
   ) ;


CREATE OR REPLACE TRIGGER  "BI_MESSAGE" 
  before insert on "MESSAGE"               
  for each row  
begin   
  if :NEW."NUM" is null then 
    select "SEQ_ID".nextval into :NEW."NUM" from dual; 
  end if; 
end; 

CREATE SEQUENCE   "SEQ_ID"  MINVALUE 1 MAXVALUE 99999999999999999 INCREMENT BY 1 START WITH 307 CACHE 20 NOORDER  NOCYCLE ;



drop table comboard;

create table comboard(
	no number,--글번호
	id         VARCHAR2(20)  NOT NULL, -- 아이디
	nickname   VARCHAR2(36)  NOT NULL,  --닉네임
	title varchar2(150) not null,  -- 글제목
	content varchar2(4000) not null,  -- 글내용
	writedate varchar2(14) not null,-- 작성일시 (yyyyMMddHHmmss)
	viewcount number not null,-- 조회수
	refamily number not null,
	restep number not null,
	relevel number not null,
	constraint comboard_pk primary key(no)
)
;
drop sequence comboard_no_seq;
create sequence comboard_no_seq
nocycle
nocache;


-- 회원
CREATE TABLE MEMBER (
	id                VARCHAR2(20)  PRIMARY KEY, -- 아이디
	password          VARCHAR2(255) NOT NULL, -- 비밀번호
	nickname          VARCHAR2(36)  NOT NULL UNIQUE, -- 닉네임
--
	name              VARCHAR2(20)  NOT NULL, -- 이름
	gender            VARCHAR2(1)   NOT NULL, -- 성별 m:남 / f:여
	birth             VARCHAR2(8)   NOT NULL, -- 생년월일
	hp                VARCHAR2(13)   NOT NULL, -- 핸드폰번호
	email             VARCHAR2(50)  NOT NULL, -- 이메일
--
	city              VARCHAR2(20)   NOT NULL, -- 시/도
	town              VARCHAR2(20)   NOT NULL, -- 구/군
	self_introduction VARCHAR2(200)         NULL,     -- 자기소개
	join_date         VARCHAR2(14)   DEFAULT to_char(sysdate, 'yyyymmddhh24miss')       NOT NULL, -- 가입일
	login_check       INTEGER  	default 0   NOT NULL, -- 로그인여부
	role		VARCHAR2(5)		DEFAULT 'user'  NOT NULL --권한 - user:회원 / admin:관리자
);
